package org.finalministry.database.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.finalministry.database.connection.IDatabaseConnection;
import org.finalministry.objects.address.NeutralPoint;
import org.finalministry.utils.geo.GeoUtils;

import com.vividsolutions.jts.io.ParseException;

public class NeutralPointDAO extends BaseDAO {

	public NeutralPointDAO(IDatabaseConnection connection) {
		super(connection);
	}

	private Long lastGeneratedId() throws SQLException, ClassNotFoundException {
		Statement stmt = getConnection().getConnection().createStatement();
		ResultSet result = stmt.executeQuery("SELECT MAX(ID) FROM NEUTRALPOINT");

		if (result.next()) {
			return result.getLong(1);
		}
		return null;
	}

	public Long create(NeutralPoint neutralPoint) throws Exception {

		String insert = "INSERT INTO NEUTRALPOINT (NAME,COORDINATES) VALUES(?,ST_GeomFromText(?,4326))";
		PreparedStatement stmt = getConnection().getConnection().prepareStatement(insert);

		stmt.setString(1, neutralPoint.getName());
		stmt.setString(2, neutralPoint.getPoint().toString());

		stmt.executeUpdate();

		stmt.close();

		return lastGeneratedId();
	}

	public void update(NeutralPoint neutralPoint) throws Exception {
		String insert = "UPDATE NEUTRALPOINT SET NAME = ?, COORDINATES = ST_GeomFromText(?,4326) WHERE ID = ?";
		PreparedStatement stmt = getConnection().getConnection().prepareStatement(insert);

		stmt.setString(1, neutralPoint.getName());
		stmt.setString(2, neutralPoint.getPoint().toString());
		stmt.setLong(3, neutralPoint.getId());

		stmt.executeUpdate();

		stmt.close();
	}

	public List<NeutralPoint> getAll() throws Exception {

		List<NeutralPoint> neutralPointList = new ArrayList<NeutralPoint>();
		Statement stmt = getConnection().getConnection().createStatement();
		ResultSet result = stmt.executeQuery("SELECT ID,NAME,ST_AsText(COORDINATES) FROM NEUTRALPOINT");

		while (result.isAfterLast() == false) {
			NeutralPoint neutralPoint = extractNeutralPointFromResult(result);

			neutralPointList.add(neutralPoint);
		}

		return neutralPointList;
	}

	public NeutralPoint getById(Long id) throws Exception {
		Statement stmt = getConnection().getConnection().createStatement();
		ResultSet result = stmt.executeQuery("SELECT ID,NAME,ST_AsText(COORDINATES) FROM NEUTRALPOINT WHERE ID = " + id);

		return extractNeutralPointFromResult(result);
	}

	public NeutralPoint getByName(String name) throws Exception {
		PreparedStatement stmt = getConnection().getConnection().prepareStatement(
				"SELECT ID,NAME,ST_AsText(COORDINATES) FROM NEUTRALPOINT WHERE NAME = ?");

		stmt.setString(1, name);

		ResultSet result = stmt.executeQuery();

		return extractNeutralPointFromResult(result);
	}

	public void delete(Long id) throws Exception {
		PreparedStatement stmt = getConnection().getConnection().prepareStatement("DELETE NEUTRALPOINT WHERE ID = ?");

		stmt.setLong(1, id);

		stmt.executeUpdate();
	}

	private NeutralPoint extractNeutralPointFromResult(ResultSet result) throws ParseException, SQLException {

		if (result.next()) {
			NeutralPoint neutralPoint = new NeutralPoint(GeoUtils.getGeometryCoordinateFromText(result.getString(3)), result.getString(2));
			neutralPoint.setId(result.getLong(1));
			return neutralPoint;
		}

		return null;
	}

}
